﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Globalization;
using System.Windows.Forms;
using Connection.ORM;
using lgmkr;

namespace Connection.DAL
{
    public class ConnectionToDB
    {
        public string ConnectionString { get; set; }

        private List<User> _users;
        private List<Number> _numbers;
        private List<Sms> _smses; 

        private SqlCeConnection _fConnection;
        private SqlCeCommand _fCommand;
        private SqlCeDataReader _fReader;
        private SqlCeDataAdapter _fAdapter;

        private LogMaker _fLogMaker;

        public ConnectionToDB()
        {
            ConnectionString = "";
        }
        public ConnectionToDB(string aConnectionString)
        {
            ConnectionString = aConnectionString;
        }

        public List<User> GetAllUser()
        {
            _users=new List<User>();
            _fConnection=new SqlCeConnection(ConnectionString);
            try
            {
                _fConnection.Open();
                _fCommand = _fConnection.CreateCommand();
                _fCommand.CommandText = "select ID,Login,Domen,Password from user";
                _fReader = _fCommand.ExecuteReader();
                while (_fReader.Read())
                {
                    var temp = new User
                                   {
                                       ID = Int32.Parse(_fReader[0].ToString()),
                                       Login = _fReader[1].ToString(),
                                       Domen = _fReader[2].ToString(),
                                       Password = _fReader[3].ToString()
                                   };
                    _users.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker {Error = e.Message};
                _fLogMaker.PrintLog();
            }
            return _users;
        }
        public List<Sms> GetAllSms()
        {
            _smses = new List<Sms>();
            _fConnection = new SqlCeConnection(ConnectionString);
            try
            {
                _fConnection.Open();
                _fCommand = _fConnection.CreateCommand();
                _fCommand.CommandText = "select ID,UserID,Text,Date,Number from Sms";
                _fReader = _fCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (_fReader.Read())
                {
                    var temp = new Sms
                    {
                        ID = Int32.Parse(_fReader[0].ToString()),
                        UserID = Int32.Parse(_fReader[1].ToString()),
                        Text = _fReader[2].ToString(),
                        Date = _fReader[3].ToString(),
                        Number = _fReader[4].ToString()
                    };
                    _smses.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker=new LogMaker {Error = e.Message};
                _fLogMaker.PrintLog();
            }
            return _smses;
        }
        public List<Number> GetAllNumbers()
        {
            _numbers = new List<Number>();
            _fConnection = new SqlCeConnection(ConnectionString);
            try
            {
                _fConnection.Open();
                _fCommand = _fConnection.CreateCommand();
                _fCommand.CommandText = "select ID,Telephone,DateOfAdd from Number";
                _fReader = _fCommand.ExecuteReader();
                while (_fReader.Read())
                {
                    var temp = new Number
                    {
                        ID = Int32.Parse(_fReader[0].ToString()),
                        Telephone = _fReader[1].ToString(),
                        DateOfAdd = DateTime.Parse(_fReader[2].ToString()),
                    };
                    _numbers.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker {Error = e.Message};
                _fLogMaker.PrintLog();
            }
            return _numbers;
        }

        public User GetUser(int id)
        {
            _users = new List<User>();
            _fConnection = new SqlCeConnection(ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            try
            {
                _fConnection.Open();
                _fCommand.CommandText = "select ID,Login,Domen,Password from user where ID="+id;
                _fReader = _fCommand.ExecuteReader();
                while (_fReader.Read())
                {
                    var temp = new User
                    {
                        ID = Int32.Parse(_fReader[0].ToString()),
                        Login = _fReader[1].ToString(),
                        Domen = _fReader[2].ToString(),
                        Password = _fReader[3].ToString()
                    };
                    _users.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker {Error = e.Message};
                _fLogMaker.PrintLog();
            }
            
            return _users[0];
        }
        public Sms GetSms(int id)
        {
            _smses = new List<Sms>();
            _fConnection = new SqlCeConnection(ConnectionString);
            try
            {
                _fConnection.Open();
                _fCommand = _fConnection.CreateCommand();
                _fCommand.CommandText = "select ID,UserID,Text,Date,Number from Sms where ID="+id;
                _fReader = _fCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (_fReader.Read())
                {
                    var temp = new Sms
                    {
                        ID = Int32.Parse(_fReader[0].ToString()),
                        UserID = Int32.Parse(_fReader[1].ToString()),
                        Text = _fReader[2].ToString(),
                        Date = _fReader[3].ToString(),
                        Number = _fReader[4].ToString()
                    };
                    _smses.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker { Error = e.Message };
                _fLogMaker.PrintLog();
            }
            return _smses[0];
        }
        public Number GetNumber(int id)
        {
            _numbers = new List<Number>();
            _fConnection = new SqlCeConnection(ConnectionString);
            try
            {
                _fConnection.Open();
                _fCommand = _fConnection.CreateCommand();
                _fCommand.CommandText = "select ID,Telephone,DateOfAdd from Number where ID="+id;
                _fReader = _fCommand.ExecuteReader();
                while (_fReader.Read())
                {
                    var temp = new Number
                    {
                        ID = Int32.Parse(_fReader[0].ToString()),
                        Telephone = _fReader[1].ToString(),
                        DateOfAdd = DateTime.Parse(_fReader[2].ToString()),
                    };
                    _numbers.Add(temp);
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker { Error = e.Message };
                _fLogMaker.PrintLog();
            }
            return _numbers[0];
        }

        public int MaxID(string tablename)
        {
            int result = 0;
            _fConnection = new SqlCeConnection(ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            try
            {
                _fConnection.Open();
                _fCommand.CommandText = "select max(ID) from "+tablename;
                _fReader = _fCommand.ExecuteReader();
                while (_fReader.Read())
                {
                    result = Int32.Parse(_fReader[0].ToString());
                }
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker { Error = e.Message };
                _fLogMaker.PrintLog();
            }
            return result;
        }

        public void AddItemDBGridView(DataGridView aDataGridView, string query)
        {
            try
            {
                _fConnection = new SqlCeConnection(ConnectionString);
                _fCommand = new SqlCeCommand { CommandText = query };
                _fAdapter = new SqlCeDataAdapter(_fCommand.CommandText, _fConnection);
                var fTable = new DataTable { Locale = CultureInfo.InvariantCulture };
                _fAdapter.Fill(fTable);
                aDataGridView.DataSource = fTable;
            }
            catch (SqlCeException e)
            {
                _fLogMaker = new LogMaker {Error = e.Message};
                _fLogMaker.PrintLog();
            }
        }

    }
}
